package com.test;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class ExcelUtil {
    private static final String REGEX = "[a-zA-Z]";

    /**
     * 导出为Excel
     *
     * @param sheetName 工作表名称
     * @param titleName 表格的标题名称
     * @param headers   表格中的每一列的列名
     * @param dataSet   要导出的数据源
     * @param resultUrl 写出Excel 的路径
     * @param pattern   指定的日期模式
     */
    public static <T> void exprotExcel(String sheetName, String titleName, String[] headers, Collection<T> dataSet, String resultUrl, String pattern) {
        doExportExcel(sheetName, titleName, headers, dataSet, resultUrl, pattern);
    }

    private static <T> void doExportExcel(String sheetName, String titleName, String[] headers, Collection<T> dataSet, String resultUrl, String pattern) {
        Sheet sheet = null;
        Row row = null;
        Workbook wb = null;
        try {
            if (StringUtil.isNotNull(resultUrl) && StringUtil.isNotNull(sheetName)) {
                wb = new XSSFWorkbook();
                sheet = wb.createSheet(sheetName);
                sheet.setDefaultColumnWidth((short) 20);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (sheet != null && titleName != null) {
            row = sheet.createRow(0);
            Cell titleCell = row.createCell(0);
            titleCell.setCellValue(titleName);
        }
        if (sheet != null) {
            row = sheet.createRow(1);
            if (null != headers) {
                for (short i = 0; i < headers.length; i++) {
                    Cell cell = row.createCell(i);
                    if (StringUtil.isNotNull(headers[i])) {
                        cell.setCellValue(headers[i]);
                    } else {
                        cell.setCellValue("");
                    }
                }
            }
        }
        Iterator<T> it = dataSet.iterator();
        int index = 1;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            Object o = it.next();
            Field[] fields = o.getClass().getDeclaredFields();
            if (row != null) {
                try {
                    for (short i = 0; i < fields.length; i++) {
                        Cell filedCell = row.createCell(i);
                        Field field = fields[i];
                        String fieldName = field.getName();
                        String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                        Class tClass = o.getClass();
                        Method getMethod = tClass.getMethod(getMethodName, new Class[]{});
                        Object value = getMethod.invoke(o, new Object[]{}); // 如果是时间类型,按照格式转换 
                        String textValue = null;
                        if (value instanceof Date) {
                            Date date = (Date) value;
                            SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                            textValue = sdf.format(date);
                        } else {
                            // 其它数据类型都当作字符串简单处理 
                            textValue = value.toString();
                        }
                        // 利用正则表达式判断textValue是否全部由数字组成 
                        if (textValue != null) {
                            Pattern p = Pattern.compile("^\\d+(\\.\\d+)?$");
                            Matcher matcher = p.matcher(textValue);
                            if (matcher.matches()) {
                                // 是数字当作double处理 
                                filedCell.setCellValue(Double.parseDouble(textValue));
                            } else {
                                // 不是数字做普通处理 
                                filedCell.setCellValue(textValue);
                            }
                            OutputStream out = null;
                            try {
                                out = new FileOutputStream(resultUrl);
                                wb.write(out);
                            } catch (IOException e) {
                                e.printStackTrace();
                            } finally {
                                try {
                                    out.close();
                                } catch (IOException e) {
                                    e.printStackTrace();
                                }
                            }
                        }
                    }
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * Excel数据导入到数据库
     * @param filePath 要导入文件的路径 
     * @param startRow 开始的行数 
     * @param endRow 结束的行数 (0表示所有行; 正数表示到第几行结束; 负数表示到倒数第几行结束) 
     * @param clazz 要返回的对象的集合类型 
     * @return 
     * @throws Exception
     */
    public static <T> List<T> importExcel(String filePath, int startRow, int endRow, Class<T> clazz, boolean showInfo) throws Exception {
        return doImportExcel(filePath, startRow, endRow, clazz, showInfo);
    }

    private static <T> List<T> doImportExcel(String filePath, int startRow, int endRow, Class<T> clazz, boolean showInfo) throws IOException {
        // 判断文件是否存在 
        File f = new File(filePath);
        if (!f.exists()) {
            throw new IOException("文件不存在");
        }
        Workbook wb = null;
        Sheet sheet = null;
        FileInputStream fileInputStream = null;
        List<Row> list = new ArrayList<>();
        try {
            fileInputStream = new FileInputStream(f);
            wb = WorkbookFactory.create(fileInputStream);
            sheet = wb.getSheetAt(0);
            // 获取最后行号 
            int lastRowNum = sheet.getLastRowNum();
            if (lastRowNum > 0) {
                // 如果>0，表示有数据 
                out("开始读取名为【" + sheet.getSheetName() + "】的内容：", showInfo);
            }
            Row row = null;
            // 循环读取 
            for (int i = startRow; i <= lastRowNum + endRow; i++) {
                row = sheet.getRow(i);
                if (row != null) {
                    list.add(row);
                    out("第" + (i + 1) + "行：", showInfo, false);
                    // 获取每一单元格的值 
                    for (int j = 0; j < row.getLastCellNum(); j++) {
                        String value = getCellValue(row.getCell(j));
                        if (!value.equals("")) {
                            out(value + " | ", showInfo, false);
                        }
                    }
                    out("", showInfo);
                }
            }
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return returnObjectList(list, clazz);
    }

    /**
     * 功能:获取单元格的值
     */
    private static String getCellValue(Cell cell) {
        Object result = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING
                    result = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    result = cell.getNumericCellValue();
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    result = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    result = cell.getCellFormula();
                    break;
                case Cell.CELL_TYPE_ERROR:
                    result = cell.getErrorCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    break;
                default:
                    break;
            }
        } return result.toString();
    }

    /**
     * 功能:输出提示信息(普通信息打印)
     */
    private static void out(String info, boolean showInfo) {
        if (showInfo) {
            System.out.print(info + (showInfo ? "" : ""));
        }
    }

    /**
     * 功能:输出提示信息(同一行的不同单元格信息打印)
     */
    private static void out(String info, boolean showInfo, boolean nextLine) {
        if (showInfo) {
            if (nextLine) {
                System.out.print(info + (showInfo ? "" : ""));
            } else {
                System.out.print(info);
            }
        }
    }

    /**
     * 功能:根据属性生成对应的set/get方法
     */
    private static <T> String convertToMethodName(String attribute, Class<T> objClass, boolean isSet) { 
        /** 通过正则表达式来匹配第一个字符 **/
        Pattern p = Pattern.compile(REGEX);
        Matcher m = p.matcher(attribute);
        StringBuilder sb = new StringBuilder(); /** 如果是set方法名称 **/if (isSet) {
            sb.append("set");
        } else { /** get方法名称 **/try {
            Field attributeField = objClass.getDeclaredField(attribute); /** 如果类型为boolean **/
            if (attributeField.getType() == boolean.class || attributeField.getType() == Boolean.class) {
                sb.append("is");
            } else {
                sb.append("get");
            }
        } catch (SecurityException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
        } /** 针对以下划线开头的属性 **/if (attribute.charAt(0) != '_' && m.find()) {
            sb.append(m.replaceFirst(m.group().toUpperCase()));
        } else {
            sb.append(attribute);
        }
        return sb.toString();
    }

    /**
     * 功能:给指定对象的指定属性赋值
     */
    private static void setAttrributeValue(Object obj, String attribute, String value) {
        //得到该属性的set方法 
        String method_name = convertToMethodName(attribute, obj.getClass(), true);
        Method[] methods = obj.getClass().getMethods();
        for (Method method : methods) { 
            /** * 因为这里只是调用bean中属性的set方法，属性名称不能重复 * 所以set方法也不会重复，所以就直接用方法名称去锁定一个方法 
             * （注：在java中，锁定一个方法的条件是方法名及参数） 
             * */
            if (method.getName().equals(method_name)) {
                Class<?>[] parameterC = method.getParameterTypes();
                try { 
                    /**如果是(整型,浮点型,布尔型,字节型,时间类型), 
                     * 按照各自的规则把value值转换成各自的类型 
                     * 否则一律按类型强制转换(比如:String类型) 
                     * */
                    if (parameterC[0] == int.class || parameterC[0] == java.lang.Integer.class) {
                        value = value.substring(0, value.lastIndexOf("."));
                        method.invoke(obj, Integer.valueOf(value));
                        break;
                    } else if (parameterC[0] == float.class || parameterC[0] == java.lang.Float.class) {
                        method.invoke(obj, Float.valueOf(value));
                        break;
                    } else if (parameterC[0] == double.class || parameterC[0] == java.lang.Double.class) {
                        method.invoke(obj, Double.valueOf(value));
                        break;
                    } else if (parameterC[0] == byte.class || parameterC[0] == java.lang.Byte.class) {
                        method.invoke(obj, Byte.valueOf(value));
                        break;
                    } else if (parameterC[0] == boolean.class || parameterC[0] == java.lang.Boolean.class) {
                        method.invoke(obj, Boolean.valueOf(value));
                        break;
                    } else if (parameterC[0] == java.util.Date.class) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        Date date = null;
                        try {
                            date = sdf.parse(value);
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                        method.invoke(obj, date);
                        break;
                    } else {
                        method.invoke(obj, parameterC[0].cast(value));
                        break;
                    }
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                } catch (SecurityException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 功能:返回指定的对象集合
     */
    private static <T> List<T> returnObjectList(List<Row> rowList, Class<T> clazz) {
        List<T> objectList = null;
        T obj = null;
        String attribute = null;
        String value = null;
        int j = 0;
        try {
            objectList = new ArrayList<>();
            Field[] declaredFields = clazz.getDeclaredFields();
            for (Row row : rowList) {
                j = 0;
                obj = clazz.newInstance();
                for (Field field : declaredFields) {
                    attribute = field.getName().toString();
                    value = getCellValue(row.getCell(j));
                    setAttrributeValue(obj, attribute, value);
                    j++;
                }
                objectList.add(obj);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return objectList;
    }
}
    中间用到了 输出，如果不想输出的话，可以采用日志(这里应用比较简单)StringUtil:


public class StringUtil {
    /*** 判断指定的字符串不为空* @param s 传入的字符串* @return 如果字符串不是空串， 剔除前后空格之后长度大于0 ， 不是null 返回true*/
    public static boolean isNotNull(String s) {
        return null != s && !s.isEmpty() && s.trim().length() > 0;
    }

    /*** 判断 s 中是否包含指定的子串* 这里进行了非空串的判断，如果不需要，则使用官方API即可。* @param s 要判断的字符串* @param subString 子串* @return 如果包含子串，返回true*/
    public static boolean containsSubString(String s, String subString) {
        return s.contains(subString) && isNotNull(s) && isNotNull(subString);
    }
}

